package com.poi.demopoi.controller;

import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import com.github.xiaoymin.knife4j.annotations.ApiSupport;
import com.google.common.collect.Lists;
import com.poi.demopoi.common.BaseController;
import com.poi.demopoi.common.page.TableDataInfo;
import com.poi.demopoi.config.aspectj.knife4j.ApiVersion;
import com.poi.demopoi.config.aspectj.knife4j.ApiVersionConstant;
import com.poi.demopoi.config.page.PageResultDTO;
import com.poi.demopoi.config.page.SmartPageUtil;
import com.poi.demopoi.domain.Employee;
import com.poi.demopoi.domain.EmployeeDto;
import com.poi.demopoi.domain.EmployeeVO;
import com.poi.demopoi.service.IEmployeeService;
import com.poi.demopoi.service.WriterService;
import com.poi.demopoi.utils.R;
import com.poi.demopoi.utils.ServletUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.bind.annotation.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.*;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.stream.Collectors;

//  falsework; Drawing board  ;
@Api(tags = {"后台-用户-员工管理"})
@ApiSupport(author = "ruoyi", order = 100)
@RestController
@RequestMapping("/emp")
public class EmployeeController extends BaseController {
    private static final Logger log = LoggerFactory.getLogger(EmployeeController.class);

    @Autowired
    private IEmployeeService userService;
    @Autowired
    private WriterService writerExcelService;

    public static Integer MAX_ROW_SHEET = 100;
    public static int BATCH_AMOUNT = 2; //一个sheet的总数量

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    DataSource dataSource;


    //&allowMultiQueries=true 小启发： https://blog.csdn.net/lianhao19900202/article/details/81015187
    @GetMapping(value = "/update1")
    public void update1() throws IOException, SQLException {
        List<Employee> userList = userService.list().subList(0, 2);
        userList.forEach(i -> i.setName(i.getName() + 2));
        userService.batchUpdateList(userList);
    }

    //&allowMultiQueries=true https://blog.csdn.net/lianhao19900202/article/details/81015187
    @GetMapping(value = "/insert3")
    public void insert3() throws IOException, SQLException {
        ArrayList<Employee> employees = Lists.newArrayList(
                new Employee() {{ setName("n1");setAge(1);setEmail("e1"); }},
                new Employee() {{ setName("n2");setAge(2);setEmail("e2"); }}
        );
        userService.batchSaveList(employees);
    }

    //insert优化： https://blog.csdn.net/qq_41204714/article/details/85634371
    // 拓展：Mysql5.7, 千万数据快速插入解决方案（ JDBC方式， 百秒搞定！！！）  https://blog.csdn.net/zhanglong_4444/article/details/86743371
    //rewriteBatchedStatements=true
    @GetMapping(value = "/insert4")
    public void insert4() throws IOException, SQLException {
        long start = System.currentTimeMillis();
        StringBuffer suffix = new StringBuffer();
        long row = 1;
        long pageBatch = 1;//共有1000个分段
        long pageSize = 3;//每个分段有10000调数据
        for (int j = 0; j < pageBatch; j++) {
            for (int i = 0; i < pageSize; i++) {
                String uuid = UUID.randomUUID().toString();
//                String name = uuid.substring(0, 4);
                String name = "name" + j + i;
                int age = i;
                String email = "email" + j + i;
//              suffix.append("(" + row + ",'" + name + "'," + sex + ",'" + phone + "','" + uuid + "'),");
                suffix.append("(" + "'" + name + "'," + age + ",'" + email + "'" + "),");

                row++;
            }
//            boolean b = userDao.insertMore1(suffix.substring(0, suffix.length() - 1), j);
//            String sql = "insert into employee (name, age, email) values (?,?,?)";
//            String sql_prefix = "insert into tuser(id,name,sex,phone,password) values ";
            String sql_prefix = "insert into employee (name, age, email) values ";
            String sql_suffix = suffix.substring(0, suffix.length() - 1);
            boolean b = affectRowMore1(sql_prefix + sql_suffix, j);

            if (!b) {
                System.out.println("出错了----");
                System.exit(0);
            } else {
                // 清空上一次添加的数据
                suffix = new StringBuffer();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("生成1000万条数据共花费" + (end - start) + "毫秒");

    }

    /**
     * 多条记录插入操作     “insert into tb (...) values(...),(...)...;”的方式
     */
    public boolean affectRowMore1(String sql, long flag) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            pstmt.addBatch(sql);
            // 执行操作
            int[] counts = pstmt.executeBatch(); // 执行Batch中的全部语句
            conn.commit(); // 提交到数据库
            for (int i : counts) {
                if (i == 0) {
                    conn.rollback();
                    return false;
                }
            }

//            closeAll(flag);
            try {
                if (conn != null && flag == -1) {
                    // 在完成批量操作后恢复默认的自动提交方式，提高程序的可扩展性
                    conn.setAutoCommit(true);
                    conn.close();
                }
                if (pstmt != null && flag == -1) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            conn.rollback();
            return false;
        }
        return true;
    }


    /**
     * 优质理论：
     * 关于jdbc批量操作(addBatch, executeBatch)的测试  https://blog.csdn.net/zhangw1236/article/details/54583192
     * JDBC实现往MySQL插入百万级数据 ： https://www.cnblogs.com/fnz0/p/5713102.html
     */
    // 不添加rewriteBatchedStatements=true参数
    @GetMapping(value = "/insert5")
    public void insert5() throws IOException, SQLException {

        List<Employee> list = new ArrayList<>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "insert into employee ( name, age, email) values (?,?,?)";
        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            ///优化插入第一步       设置手动提交
            conn.setAutoCommit(false);

            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
//            int len = list.size();
            int len = 3; // 条数： 3+1

            //————方式1：————————————————————————————————————————————————————
            //https://www.bbsmax.com/A/n2d9jQO6zD/
            //https://blog.csdn.net/u011402896/article/details/79905845
            for (int i = 0; i <= len; i++) {
                // pstmt.setLong(1, i);
                pstmt.setString(1, "name_" + i);
                pstmt.setInt(2, i);
                pstmt.setString(3, "email_" + i);
                ////优化插入第二步       插入代码打包，等一定量后再一起插入。
                pstmt.addBatch();

                //当积攒到一定程度，统一执行一次，并且清空之前”积攒“的sql
                //每200次提交一次  //小批量提交,避免OOM
//                if ((i != 0 && i % 200 == 0) || i == len - 1) { //可以设置不同的大小；如50，100，200，500，1000等等
                if ((i % 200 == 0)) { //可以设置不同的大小；如50，100，200，500，1000等等
                    pstmt.executeBatch();//批量执行预定义SQL
                    //优化插入第三步       提交，批量插入数据库中。
                    conn.commit();
                    pstmt.clearBatch();//提交后，Batch清空。
                }
            }
            //————方式1：————————————————————————————————————————————————————

//            //————方式2：————————————————————————————————————————————————————
//            //https://developer.aliyun.com/article/455633
//            //https://blog.csdn.net/jingzi123456789/article/details/80686507
//            //https://www.bbsmax.com/A/q4zV8Va25K/
//            for (int i = 0; i <= len; i++) {
//                // pstmt.setLong(1, i);
//                pstmt.setString(1, "name_" + i);
//                pstmt.setInt(2, i);
//                pstmt.setString(3, "email_" + i);
//                //”积攒“sql: 添加一次预定义参数
//                pstmt.addBatch();
//
//                //当 "积攒" 到一定程度, 就统一的执行一次. 并且清空先前 "积攒" 的 SQL
//                if((i + 1) % 200 == 0){
//                    pstmt.executeBatch();
//                    pstmt.clearBatch();
//                }
//
//            }
            //            //————方式2：————————————————————————————————————————————————————


            //若总条数不是批量数值的整数倍, 则还需要再额外的执行一次.
            if (len % 200 != 0) {
                pstmt.executeBatch();//若总条数不是批量数值的整数倍，还需要在执行一次
                pstmt.clearBatch();//提交后，Batch清空。
            }
            conn.commit();//优化插入第三步提交，批量插入数据库中。


            Long endTime = System.currentTimeMillis();
            log.info("OK,用时：" + (endTime - startTime));

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
                // TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();  方式1
                throw new RuntimeException(e);//回滚事务 方式2
            }
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

//    //https://blog.csdn.net/panyongcsd/article/details/93853687
//    @GetMapping(value = "/monthly")
//    public void getFinanceMonthly(Employee userDTO, @RequestParam("startDate") String startDateStr, @RequestParam("endDate") String endDateStr, HttpServletResponse response) {
//
//        try {
//            String name = userDTO.getName();
//            ServletOutputStream writer = null;
//            log.info(String.format("getFinanceMonthly(),日期:{%s}--{%s}", startDateStr, endDateStr));
//
//            Date startDate = new Date(startDateStr);
//            Date endDate = new Date(endDateStr);
//
//            Long dataQueryStartTS = System.currentTimeMillis();
//            log.info("查询并构建数据开始");
//
//
//            LambdaQueryWrapper<Employee> lambdaQuery = Wrappers.lambdaQuery();
//            lambdaQuery
//                    .like(StringUtils.isNotBlank(name), Employee::getName, name)
//                    .orderByDesc(Employee::getAge)
//            ;
//            //获取当前条件下数据总条数
//            Integer total = userService.count(lambdaQuery);
//            if (total == null || total == 0) {
//                writer.write("数据为空".getBytes("gbk"));
//                writer.flush();
//                return;
//            }
//
//            String fileName = String.format("Monthly_Finance_%s.xlsx", endDateStr);
//            InputStream fs = new ClassPathResource("template/orderFinanceStatistics.xlsx").getInputStream();
//            XSSFWorkbook wb = new XSSFWorkbook(fs);
//            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(wb, 1000);
//            response.setCharacterEncoding("utf-8");
//            response.setContentType("application/vnd.ms-excel");
//            response.setHeader("Content-Disposition", "attachment;fileName="
//                    + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
//
//            //根据总条数设置任务数量和生产者、消费者个数
//            Integer taskCount = total % BATCH_AMOUNT == 0 ? total / BATCH_AMOUNT : (total / BATCH_AMOUNT) + 1;
//
//            //构建框架的请求参数
////            Map<String, String> businessParams = new HashMap<>();
////            businessParams.put("startDate", startDateStr);
////            businessParams.put("endDate", endDateStr);
//
//            //初始化导出框架
//            ExportConfigThread configThread = new ExportConfigThread("财务统计", total, BATCH_AMOUNT, userDTO,
//                    sxssfWorkbook, userService, writerExcelService);
//            //启动导出线程并使主线程等待该线程完成
//            configThread.start();
//            //确保主线程的后续操作在 消费者线程结束(即数据都写入row)后再执行
//            configThread.join();
//
//            Long dataWriteStartTS = System.currentTimeMillis();
//            log.info(String.format("数据写入结束,耗时：%s ms", System.currentTimeMillis() - dataWriteStartTS));
//
//            sxssfWorkbook.write(writer);
//            writer.flush();
//            sxssfWorkbook.dispose();
//
//            Long dataQueryEndTS = System.currentTimeMillis();
//            log.info(String.format("数据查询构建成功，耗时：%s ms", dataQueryEndTS - dataQueryStartTS));
//        }catch (
//                IOException e){
//            log.error("writer() 异常-->",e);
//        } catch (InterruptedException e) {
//            log.error("消费者异常--->",e);
//        } finally {
//            if (writer != null){
//                try {
//                    writer.close();
//                } catch (IOException e) {
//                    log.error("关闭writer 异常-->",e);
//                }
//            }
//        }
//    }

    /**
     * ★★★★★★★★★★
     * SXSSF 探索好文：
     * <p>
     * 【知识积累】报表优化 - 使用SXSSF分批处理大数据量EXCEL导出  https://www.freesion.com/article/1910194826/
     * POI操作Excel2007实例二之 Sxssfworkboo k 处理海量数据  https://blog.csdn.net/little_stars/article/details/8266262
     * Java之Poi大数据量导出技术SXSSFWorkbook应用  https://blog.csdn.net/qq_23303245/article/details/88707965
     * 为什么以SXSSFWorkbook来导出excel仍然出现内存溢出的现象？  :  https://ask.csdn.net/questions/709853?sort=votes_count
     * <p>
     * <p>
     * https://bbs.csdn.net/topics/392481094?list=lz  HSSFWorkbook 导出 excel 表格 内容 乱码!!! 求救！！ 大神们！！
     * https://my.oschina.net/ffse54s/blog/3043562  EasyExcel使用记录
     * <p>
     * 拓展：
     * EasyPOI导出Excel模板数据（含图片） https://blog.csdn.net/u012441819/article/details/96828044 (不如直接用poi!!!)
     */
    //cxcel文件 接口没有返回值的写法
    @GetMapping(value = "/export3/users/download/local/no")
    public void export3(EmployeeDto dto, HttpServletResponse response, HttpServletRequest request) throws IOException {
        try {

            String name = dto.getName();
            Long dataQueryStartTS = System.currentTimeMillis();
            log.info("查询并构建数据开始, {0}", "");


            LambdaQueryWrapper<Employee> lambdaQuery = Wrappers.lambdaQuery();
            lambdaQuery
                    .like(StringUtils.isNotBlank(name), Employee::getName, name)
                    .orderByDesc(Employee::getAge)
            ;
            //获取当前条件下数据总条数
            int total = userService.count(lambdaQuery);
            //todo 1


            InputStream fs = new ClassPathResource("/export_templates/stockExportAmount21.xlsx").getInputStream();
            XSSFWorkbook wb = new XSSFWorkbook(fs);
            //引入SXSSFWorkbook，利用其高效的Excel数据处理特性
            //使用教程1： https://www.lizenghai.com/archives/39681.html
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(wb, 1000);
            //todo 2

            //分段数： 根据总条数设置任务数量和生产者、消费者个数
            Integer taskCount = total % BATCH_AMOUNT == 0 ? total / BATCH_AMOUNT : (total / BATCH_AMOUNT) + 1;

            //把任务 分片后 交给线程池执行并得到Future 分段集合
            ExecutorService sqlTaskExecutorService = Executors.newFixedThreadPool(5);
            List<Future<List<EmployeeVO>>> userPagesFutureList = new ArrayList<>(taskCount);
            for (int i = 1; i <= taskCount; i++) {
                //物理分页
                StatisticsTask userPagesCallable = new StatisticsTask(userService, i, dto);
                Future<List<EmployeeVO>> userPagesFuture = sqlTaskExecutorService.submit(userPagesCallable);
                userPagesFutureList.add(userPagesFuture);
            }
            sqlTaskExecutorService.shutdown();


            //Producer负责吧数据集合放入queue : 遍历Future集合,交给生产者处理，等待结果后放入阻塞队列
            ArrayBlockingQueue<List<EmployeeVO>> queue = new ArrayBlockingQueue<>(10);
            ExecutorService producerExecutorService = Executors.newFixedThreadPool(5);
            for (int i = 0; i < taskCount; i++) {
                Producer producer = new Producer(queue, userPagesFutureList.get(i), "producer_" + i);
                producerExecutorService.submit(producer);
            }
            producerExecutorService.shutdown();


            //消费者开始 消费 阻塞队列queue 中的数据
            log.info("开始写入数据, {0}", "");
            Long dataWriteStartTS = System.currentTimeMillis();
            Consumer consumer = new Consumer(queue, sxssfWorkbook, total);
            consumer.start();
            //确保主线程的后续操作在消费者线程结束(即数据都写入row)后再执行
            consumer.join();
            log.info("数据写入结束,耗时：%s ms", (System.currentTimeMillis() - dataWriteStartTS));

            String filename = String.format("员工列表31", "");

            String fileNameEn = encodingFilename(filename);


            //方式1
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileNameEn, "UTF-8"));

            ServletOutputStream outputWriter = response.getOutputStream();
            sxssfWorkbook.write(outputWriter);

            Sheet sheet = wb.createSheet();
            sheet.setDefaultColumnWidth((short) 15);

//            String sheetName2 = filename.replace("xlsx", "");
            String sheetName2 = "ha2";
            sxssfWorkbook.setSheetName(0, sheetName2);
            String header = request.getHeader("User-Agent").toUpperCase();
            if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
                filename = URLEncoder.encode(filename, "utf-8");
                filename = filename.replace("+", "%20");
            }
            response.setContentType(String.format("%s;charset=utf-8", "application/x"));
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    new String(filename.getBytes("utf-8"), "iso8859-1"));
//        response.setHeader("Content-Length", String.valueOf(outputWriter.toByteArray().length));
//        response.getOutputStream().write(output.toByteArray());

            //方式2
//            ByteArrayOutputStream outputWriter = new ByteArrayOutputStream();
//            sxssfWorkbook.write(outputWriter);
//            String filename3 = String.format("培训班详细考勤情况-%s.xlsx", "");
//            String sheetName = filename3.replace("xlsx", "");
//            sxssfWorkbook.setSheetName(0, sheetName);
//            String header = request.getHeader("User-Agent").toUpperCase();
//            if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
//                filename3 = URLEncoder.encode(filename3, "utf-8");
//                filename3 = filename3.replace("+", "%20");
//            }
//
//            response.setContentType(String.format("%s;charset=utf-8", "application/x"));
//            response.setHeader("Content-Disposition",
//                    "attachment;filename=" + new String(filename3.getBytes("utf-8"), "iso8859-1"));
////            String encodeName = URLEncoder.encode(filename3, StandardCharsets.UTF_8.toString());
////            response.setHeader("Content-Disposition", "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);
//
//            response.setHeader("Content-Length", String.valueOf(outputWriter.toByteArray().length));
//            response.getOutputStream().write(outputWriter.toByteArray());


            outputWriter.flush();
            outputWriter.close();
            sxssfWorkbook.dispose(); // 从磁盘上释放临时文件

            Long dataQueryEndTS = System.currentTimeMillis();
            log.info("数据查询构建成功，耗时：%s ms", (dataQueryEndTS - dataQueryStartTS));


//        }catch (IOException e){
//            //todo

            //有返回值会抛错：HttpMessageNotWritableException: with preset Content-Type 'application/x;charset=utf-8'
//            return R.ok(fileNameEn);
        } catch (Exception e) {
            log.error("导出异常", e);
//            return R.error("导出异常");
        }
    }


    //excel下载到当前项目下的写法  /boot-plus-diy/ruoyi/uploadPath/download
    @GetMapping(value = "/export2/users/download/local")
    public R export2(EmployeeDto dto, HttpServletResponse response, HttpServletRequest request) throws IOException {
        try {

            String name = dto.getName();
            Long dataQueryStartTS = System.currentTimeMillis();
            log.info("查询并构建数据开始, {0}", "");


            LambdaQueryWrapper<Employee> lambdaQuery = Wrappers.lambdaQuery();
            lambdaQuery
                    .like(StringUtils.isNotBlank(name), Employee::getName, name)
                    .orderByDesc(Employee::getAge)
            ;
            //获取当前条件下数据总条数
            int total = userService.count(lambdaQuery);
            //todo1


            InputStream fs = new ClassPathResource("/export_templates/stockExportAmount21.xlsx").getInputStream();
            XSSFWorkbook wb = new XSSFWorkbook(fs);
            //引入SXSSFWorkbook，利用其高效的Excel数据处理特性
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(wb, 1000);
            //todo2

            //根据总条数设置任务数量和生产者、消费者个数
            Integer taskCount = total % BATCH_AMOUNT == 0 ? total / BATCH_AMOUNT : (total / BATCH_AMOUNT) + 1;


            //把任务分片后交给线程池执行并得到Future集合
            ExecutorService sqlTaskExecutorService = Executors.newFixedThreadPool(5);
            List<Future<List<EmployeeVO>>> userPagesFutureList = new ArrayList<>(taskCount);
            for (int i = 1; i <= taskCount; i++) {
                //物理分页
                StatisticsTask userPagesCallable = new StatisticsTask(userService, i, dto);
                Future<List<EmployeeVO>> userPagesFuture = sqlTaskExecutorService.submit(userPagesCallable);
                userPagesFutureList.add(userPagesFuture);
            }
            sqlTaskExecutorService.shutdown();


            //生产者负责吧数据集合放入queue : 遍历Future集合,交给生产者处理，等待结果后放入阻塞队列
            ArrayBlockingQueue<List<EmployeeVO>> queue = new ArrayBlockingQueue<>(10);
            ExecutorService producerExecutorService = Executors.newFixedThreadPool(5);
            for (int i = 0; i < taskCount; i++) {
                Producer producer = new Producer(queue, userPagesFutureList.get(i), "producer_" + i);
                producerExecutorService.submit(producer);
            }
            producerExecutorService.shutdown();


            //消费者开始 消费 阻塞队列queue 中的数据
            log.info("开始写入数据, {0}", "");
            Long dataWriteStartTS = System.currentTimeMillis();
            Consumer consumer = new Consumer(queue, sxssfWorkbook, total);
            consumer.start();
            //确保主线程的后续操作在消费者线程结束(即数据都写入row)后再执行
            consumer.join();
            log.info("数据写入结束,耗时：%s ms", (System.currentTimeMillis() - dataWriteStartTS));

            String filename = String.format("库存价值表32", "");

            String fileNameEn = encodingFilename(filename);
            String fileNameEn2 = getAbsoluteFile(fileNameEn);
            OutputStream outputWriter = new FileOutputStream(fileNameEn2);
//            ServletOutputStream outputWriter = response.getOutputStream();

            response.setCharacterEncoding("UTF-8");
//            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileNameEn2, "UTF-8"));

            sxssfWorkbook.write(outputWriter);

            String sheetName0 = filename.replace("xlsx", "");
            sxssfWorkbook.setSheetName(0, sheetName0);
            String header = request.getHeader("User-Agent").toUpperCase();
            if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
                filename = URLEncoder.encode(filename, "utf-8");
                filename = filename.replace("+", "%20");
            }
            response.setContentType(String.format("%s;charset=utf-8", "application/x"));
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    new String(filename.getBytes("utf-8"), "iso8859-1"));

            outputWriter.flush();
            sxssfWorkbook.dispose();

            Long dataQueryEndTS = System.currentTimeMillis();
            log.info("数据查询构建成功，耗时：%s ms", (dataQueryEndTS - dataQueryStartTS));

            return R.ok(fileNameEn);
        } catch (Exception e) {
            log.error("导出异常", e);
            return R.error("导出异常");
        }
    }

    public String encodingFilename(String filename) {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }

    //获取下载路径
    public String getAbsoluteFile(String filename) {
//        String downloadPath = RuoYiConfig.getDownloadPath() + filename;
        String downloadPath = "ruoyi/uploadPath" + "/download/" + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }


    /**
     * 完美
     * 核心：https://github.com/jbuider1993/smart-admin1
     * 多条件排序  https://www.freesion.com/article/4897968680/ 京淘实训DAY08-京淘后台项目维护
     * 啰嗦的其他方式：https://blog.csdn.net/weixin_37460672/article/details/105854868
     * {
     * "id": 0,
     * "name": "",
     * "orders": [
     * {
     * "asc": true,
     * "column": "age"
     * }
     * ],
     * "current": 1,
     * "size": 10,
     * "searchCount": true
     * }
     */
    @ApiOperation(value = "分页-多字段排序32", notes = "★★★完美★★★", response = Employee.class)
    @ApiOperationSupport(order = 10, author = "ruoyi", ignoreParameters = {"searchValue", "searchValue", "createBy", "updateBy", "updateTime", "remark", "dataScope", "beginTime", "endTime"})
    @ApiVersion({ApiVersionConstant.V_3_0_0})
    @PostMapping("/pages/orders/32")
    public R pagesOrders32(@RequestBody Employee userDTO) {
        Integer pageNo = userDTO.getCurrent();
        Integer pageSize = userDTO.getSize();
        Page<Employee> paginationPage = new Page<>(pageNo, pageSize);

//        OrderItem orderItem1 = new OrderItem();
//        orderItem1.setColumn("age")
//                .setAsc(false);
//        OrderItem orderItem2 = new OrderItem();
//        orderItem2.setColumn("name")
//                .setAsc(false);
//        page.addOrder(orderItem1, orderItem2);

//        List<OrderItem> orderItemList2 = Lists.newArrayList(OrderItem.desc("age"));
//        paginationPage.setOrders(orderItemList2);


        List<OrderItem> orders = userDTO.getOrders();
        if (CollectionUtils.isNotEmpty(orders)) {
            List<OrderItem> orderItemList = orders.stream().map(e -> convertOrderItem(e)).collect(Collectors.toList());
//            paginationPage.setOrders(orderItemList);
            paginationPage.addOrder(orderItemList);
        }

        LambdaQueryWrapper<Employee> lambdaQuery = Wrappers.lambdaQuery();

        return R.ok(userService.getUsersWithRolePage02(paginationPage, lambdaQuery));
    }

    /**
     * 分页-多字段排序-自定义 Util
     * --- 功能已实现 待优化
     * 多条件排序root 核心： https://github.com/jbuider1993/smart-admin1/blob/0646df4d8e1c2a97770ad7715b0cff82975a5feb/smart-admin-service/smart-admin-api/src/main/java/net/lab1024/smartadmin/util/SmartPageUtil.java
     * [go to file]:    smart-admin1 /  src/main/java/net/lab1024
     * {
     * "orders": [
     * {
     * "asc": false,
     * "column": "age"
     * }
     * ],
     * "current": 1,
     * "size": 10,
     * "searchCount": true
     * }
     */
    @ApiOperation(value = "分页-多字段排序31", notes = "自定义分页字段PageUtil(功能已实现 待优化)", response = Employee.class)
    @ApiOperationSupport(order = 10, author = "ruoyi", ignoreParameters = {"searchValue", "searchValue", "createBy", "updateBy", "updateTime", "remark", "dataScope", "beginTime", "endTime"})
    @ApiVersion({ApiVersionConstant.V_3_0_0})
    @PostMapping("/pages/orders/31")
    public R pagesOrders31(@RequestBody EmployeeDTO2 queryDTO) {
        return R.ok(userService.queryByPage(queryDTO));
    }

    //废弃
    @ApiOperation(value = "分页-多字段排序30", notes = "废弃-过渡使用", response = Employee.class)
    @ApiOperationSupport(order = 10, author = "ruoyi", ignoreParameters = {"searchValue", "searchValue", "createBy", "updateBy", "updateTime", "remark", "dataScope", "beginTime", "endTime"})
    @ApiVersion({ApiVersionConstant.V_3_0_0})
    @PostMapping("/pages/orders/30")
    public R pagesOrders30(@RequestBody Employee userDTO) {
        String name = userDTO.getName();
        LambdaQueryWrapper<Employee> lambdaQuery = Wrappers.lambdaQuery();
        lambdaQuery
                .eq(StringUtils.isNotBlank(name), Employee::getName, name)
                .eq(null != userDTO.getAge(), Employee::getName, userDTO.getAge())
                .orderByDesc(Employee::getAge)
        ;

        Integer pageNo = userDTO.getCurrent();
        Integer pageSize = userDTO.getSize();
        Page<Employee> paginationPage = new Page<>(pageNo, pageSize);

        List<OrderItem> orders = userDTO.getOrders();
//        if (CollectionUtils.isNotEmpty(orders)) {
////            List<OrderItem> orderItemList = orders.stream().map(e -> convertOrderItem(e)).collect(Collectors.toList());
//            List<OrderItem> orderItemList = orders.stream().map(e -> {
//                OrderItem orderItemDTO =  new OrderItem();
//                orderItemDTO.setColumn(e.getColumn());
//                if (e.isAsc()) {
//                    return orderItemDTO.asc(orderItemDTO.getColumn());
//                } else {
//                    return orderItemDTO.desc(orderItemDTO.getColumn());
//                }
//            }).collect(Collectors.toList());
////            paginationPage.setOrders(orderItemList);
////            paginationPage.addOrder(orderItemList);
//        }

//        ArrayList<OrderItem> orderItemList2 = Lists.newArrayList(OrderItem.asc("age"));
//        paginationPage.addOrder(orderItemList2);


//        List<OrderItem> orderItemList3 = new ArrayList<>();
//        OrderItem orderItem = new OrderItem();
//        orderItem.setColumn("age");
////        orderItem.setColumn(StrUtil.toUnderlineCase(pageModel.getSort()));
//
//        orderItem.setAsc(true);
//        orderItemList3.add(orderItem);
//        paginationPage.addOrder(orderItemList3);


//        paginationPage.addOrder(OrderItem.asc("age"));


        OrderItem orderItem = new OrderItem();
        orderItem.setColumn("age")
                .setAsc(true);
        paginationPage.addOrder(orderItem);

        // 用在当前方法中无效果！- 分页-多字段排序
        Page<Employee> pageResult = userService.page(paginationPage, lambdaQuery);
        return R.ok(pageResult);
    }

    //普通分页-1-mybatisPlus-【userService.page(new com.baomidou.mybatisplus.extension.plugins.pagination.Page<>(pageNo, pageSize), lambdaQuery)】
    @ApiOperation(value = "普通分页-mybatisPlus★★★完美★★★", notes = "方式2-【userService.page(new com.baomidou.mybatisplus.extension.plugins.pagination.Page<>(pageNo, pageSize), lambdaQuery)】", response = Employee.class)
    @ApiOperationSupport(order = 10, author = "ruoyi", ignoreParameters = {"searchValue", "searchValue", "createBy", "updateBy", "updateTime", "remark", "dataScope", "beginTime", "endTime"})
    @ApiVersion({ApiVersionConstant.V_3_0_0})
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "ext5", value = "扩展测试字段", required = true, dataType = "String", example = "1")
    })
    @GetMapping("/pages3")
    public R pages3(Employee userDTO,
                    @RequestParam(value = "current", defaultValue = "1") Integer pageNo,
                    @RequestParam(value = "size", defaultValue = "30") Integer pageSize) {
        String name = userDTO.getName();
        LambdaQueryWrapper<Employee> lambdaQuery = Wrappers.lambdaQuery();
        lambdaQuery
                .eq(StringUtils.isNotBlank(name), Employee::getName, name)
                .eq(null != userDTO.getAge(), Employee::getName, userDTO.getAge())
//                .ge(GyStockOut::getOutDate, String.format("%s %s", timeStartFormate, "00:00:00"))
//                .le(GyStockOut::getOutDate, String.format("%s %s", timeEndFormate, "23:59:59"))
                .orderByDesc(Employee::getAge)
        ;

//        if (StringUtils.isNotBlank(name)) {
//            lambdaQuery.and(wrapper -> wrapper.like(Employee::getName, name)
//                    .or().like(Employee::getName, name)
//                    .or().like(Employee::getFiveCode, name)
//                    .or().like(Employee::getPinyinCode, name)
//            );
//        }
        lambdaQuery.orderByDesc(Employee::getAge);

        Page<Employee> paginationPage = new Page<>(pageNo, pageSize);

//        // 用在当前方法中无效果！
//        List<OrderItem> orders = userDTO.getOrders();
//        if (CollectionUtils.isNotEmpty(orders)) {
//            List<OrderItem> orderItemList = orders.stream().map(e -> convertOrderItem(e)).collect(Collectors.toList());
//            paginationPage.setOrders(orderItemList);
//        }

        // 用在当前方法中无效果！- 分页-多字段排序
        Page<Employee> pageResult = userService.page(paginationPage, lambdaQuery);
        return R.ok(pageResult);
    }

    //多字段排序分页逻辑处理
    private static OrderItem convertOrderItem(OrderItem orderItemDTO) {
        if (orderItemDTO.isAsc()) {
            return OrderItem.asc(orderItemDTO.getColumn());
        } else {
            return OrderItem.desc(orderItemDTO.getColumn());
        }
    }

    //分页-2-mybatisPlus【 IPage<Employee> getUserVosPage(com.baomidou.mybatisplus.extension.plugins.pagination.Page page, @Param("query") Employee userDTO);】
    @ApiOperation(value = "普通分页-mybatisPlus", notes = "方式2-【 IPage<Employee> getUserVosPage(com.baomidou.mybatisplus.extension.plugins.pagination.Page page, @Param(\"query\") Employee userDTO);】", response = Employee.class)
    @ApiOperationSupport(order = 10, author = "ruoyi", ignoreParameters = {"searchValue", "searchValue", "createBy", "updateBy", "updateTime", "remark", "dataScope", "beginTime", "endTime"})
    @ApiVersion({ApiVersionConstant.V_3_0_0})
    @GetMapping("/pages2")
    public R pages2(Page page, Employee userDTO) {
        return R.ok(userService.getUsersWithRolePage(page, userDTO));
    }


    //分页-3-若依-自定义分页格式-字段
    @ApiOperation(value = "普通分页-ruoyi", notes = "自定义分页字段PageUtil", response = Employee.class)
    @ApiOperationSupport(order = 10, author = "ruoyi", ignoreParameters = {"searchValue", "searchValue", "createBy", "updateBy", "updateTime", "remark", "dataScope", "beginTime", "endTime"})
    @ApiVersion({ApiVersionConstant.V_3_0_0})
    @GetMapping("/pages1/ruoyi")
    public R pages1Ruoyi(Employee employee) {
        startPage();
//        Assert.isTrue(1 > 2,"ha1{}ha2{}","-b1-","-b2-");

        //todo...   LambdaQueryWrapper<Employee> lambdaQuery = Wrappers.lambdaQuery();
        // baseMapper.selectList(Wrappers.<SysUser>lambdaQuery().
        //         isNotNull(SysUser::getNo).
        //         ne(SysUser::getBatchNumber,"").
        //         orderByDesc(SysUser::getCreateTime).last("limit 1"));
        List<Employee> list = userService.selectEmployeePagesRuoyi(employee);
        return R.ok(getDataTable(list));
    }

    @GetMapping("/pages12/ruoyi")
    public R pages1Ruoyi12(Employee employee) {
        startPage2();
        List<Employee> list = userService.selectEmployeePagesRuoyi(employee);
        return R.ok(getDataTable2(list));
    }


    @PostMapping
    public R insert(@RequestBody Employee userDto) {
        return R.ok(userService.save(userDto));
    }


    @PutMapping
    public R update(@RequestBody Employee userDto) {
        return R.ok(userService.updateById(userDto));
    }

    @DeleteMapping("/{userId}")
    public R delete(@PathVariable("userId") Integer userId) {
        return R.ok(userService.removeById(userId));
    }


    @GetMapping("/info/{id}")
    public R getList(@PathVariable("id") String id) {
        return R.ok(userService.getById(id));
    }

    /**
     * 修改密码
     *
     * @return
     */
//    @SysOperaLog(descrption = "修改密码")
//    @PutMapping("updatePass")
//    @PreAuthorize("hasAuthority('sys:user:updatePass')")
//    public R updatePass(@RequestParam String oldPass, @RequestParam String newPass) {
//        // 校验密码流程
//        SysUser sysUser = userService.findSecurityUserByUser(new SysUser().setUsername(SecurityUtil.getUser().getUsername()));
//        if (!PreUtil.validatePass(oldPass, sysUser.getPassword())) {
//            throw new PreBaseException("原密码错误");
//        }
//        if (StrUtil.equals(oldPass, newPass)) {
//            throw new PreBaseException("新密码不能与旧密码相同");
//        }
//        // 修改密码流程
//        SysUser user = new SysUser();
//        user.setUserId(sysUser.getUserId());
//        user.setPassword(PreUtil.encode(newPass));
//        return R.ok(userService.updateUserInfo(user));
//    }

    /**
     * 检测用户名是否存在 避免重复
     *
     * @param userName
     * @return
     */
//    @PostMapping("/vailUserName")
//    public R vailUserName(@RequestParam String userName) {
//        SysUser sysUser = userService.findSecurityUserByUser(new SysUser().setUsername(userName));
//        return R.ok(ObjectUtil.isNull(sysUser));
//    }

//
//
//    /**
//     * 修改密码
//     *
//     * @return
//     */
//    @SysOperaLog(descrption = "修改邮箱")
//    @PutMapping("updateEmail")
//    @PreAuthorize("hasAuthority('sys:user:updateEmail')")
//    public R updateEmail(@RequestParam String mail, @RequestParam String code, @RequestParam String pass, HttpServletRequest request) {
//        // 校验验证码流程
//        String ccode = (String) request.getSession().getAttribute(PreConstant.RESET_MAIL);
//        if (ObjectUtil.isNull(ccode)) {
//            throw new PreBaseException("验证码已过期");
//        }
//        if (!StrUtil.equals(code.toLowerCase(), ccode)) {
//            throw new PreBaseException("验证码错误");
//        }
//        // 校验密码流程
//        SysUser sysUser = userService.findSecurityUserByUser(new SysUser().setUsername(SecurityUtil.getUser().getUsername()));
//        if (!PreUtil.validatePass(pass, sysUser.getPassword())) {
//            throw new PreBaseException("密码错误");
//        }
//        // 修改邮箱流程
//        SysUser user = new SysUser();
//        user.setUserId(sysUser.getUserId());
//        user.setEmail(mail);
//        return R.ok(userService.updateUserInfo(user));
//    }


}
